/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.webaccess.retailws.DAO;

import com.webaccess.retailws.Bean.PaymentBean;
import com.webaccess.retailws.Util.CommonUtil;
import com.webaccess.retailws.db.DBConnection;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 *
 * @author Owner
 */
public class PaymentDAO {

    DBConnection dbc = null;
    Connection conn = null;
    ResultSet rs = null;
    String result = null;
    StringBuffer myQuery = null;
    PaymentBean payBean = null;
    CommonUtil commUtil = null;
    CommonDAO commDAO = null;

    public PaymentDAO() {
        try {
            dbc = new DBConnection();
            //conn = (Connection) dbc.getConnection();
            commUtil = new CommonUtil();
            commDAO = new CommonDAO();
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }

    public PaymentBean getPaymentDelivery(Long cartId) {
        payBean = new PaymentBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectpaymentdelivery('");
            myQuery.append(cartId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    payBean = setPaymentDelivery(rs);
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return payBean;
        }
    }

    private PaymentBean setPaymentDelivery(ResultSet rs) {
        try {
            while (rs.next()) {
                payBean = new PaymentBean();
                payBean.setTotalItem(rs.getString("noofitem"));
                payBean.setPaydelSubTotal(rs.getString("cartcost"));
                payBean.setShippingCost(String.valueOf("0"));
                payBean.setPaydelTotal(String.valueOf(rs.getLong("cartcost") + 0));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return payBean;
        }
    }

    public Long savePaymentDelivery(PaymentBean payBean, Long userId) {
        Long paymentId = 0L;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_savepayment('");
            myQuery.append(payBean.getPaymentId());
            myQuery.append("','");
            if (payBean.getPaymentType().equalsIgnoreCase("card")) {
                myQuery.append("1','");
            } else if (payBean.getPaymentType().equalsIgnoreCase("bank")) {
                myQuery.append("2','");
            } else if (payBean.getPaymentType().equalsIgnoreCase("cash")) {
                myQuery.append("3','");
            }
            myQuery.append(userId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    while (rs.next()) {
                        paymentId = rs.getLong("sp_PaymentId");
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return paymentId;
        }

    }

    public Long getPaymentType(Long paymentId) {
        Long paymentType = 0L;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("SELECT `paymentmode_id` 	FROM 	`retailws`.`payment` WHERE `payment_id` ='");
            myQuery.append(paymentId);
            myQuery.append("' AND `datastatus_id` != '3'");
            rs = dbc.executeQuery(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    while (rs.next()) {
                        paymentType = rs.getLong("paymentmode_id");
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return paymentType;
        }
    }

    public Long saveOrder(Long userId, Long paymentId, Long cartId, String paydelTotal) {
        Long orderId = 0L;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_saveOrder(NULL,'1','");
            myQuery.append(commUtil.getCurrentDateTime());
            myQuery.append("','");
            myQuery.append(paydelTotal);
            myQuery.append("','");
            myQuery.append(paymentId);
            myQuery.append("','");
            myQuery.append(userId);
            myQuery.append("','");
            myQuery.append(cartId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    while (rs.next()) {
                        orderId = rs.getLong("sp_OrderId");
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return orderId;
        }
    }

    public LinkedHashMap getBankLHMap() {
        LinkedHashMap bankLHMap = new LinkedHashMap();
        myQuery = new StringBuffer();
        myQuery.append("SELECT 	`bank_id`, 	`bankname`	FROM 	`retailws`.`bank`");
        bankLHMap = commDAO.getData(myQuery.toString());
        return bankLHMap;
    }

    public LinkedHashMap getCardTypeLHMap() {
        LinkedHashMap cardTypeLHMap = new LinkedHashMap();
        myQuery = new StringBuffer();
        myQuery.append("SELECT 	`cardtype_id`, 	`cardtye`	 FROM 	`retailws`.`cardtype` ");
        cardTypeLHMap = commDAO.getData(myQuery.toString());
        return cardTypeLHMap;
    }

    public PaymentBean getOrder(Long userId, Long paymentId, Long cartId) {
        payBean = new PaymentBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("SELECT 	`order_id`,`invoiceamt` FROM `retailws`.`order`  WHERE 	`payment_id` ='");
            myQuery.append(paymentId);
            myQuery.append("'AND 	`user_id`='");
            myQuery.append(userId);
            myQuery.append("'AND 	`cart_id`='");
            myQuery.append(cartId);
            myQuery.append("'");
            rs = dbc.executeQuery(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    payBean = setOrder(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return payBean;
        }
    }

    private PaymentBean setOrder(ResultSet rs) {
        try {
            while (rs.next()) {
                payBean = new PaymentBean();
                payBean.setOrderId(rs.getLong("order_id"));
                payBean.setPaydelTotal(rs.getString("invoiceamt"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return payBean;
        }
    }

    public boolean savePaymentCard(PaymentBean payBean, Long orderId, Long cartId) {
        Long paymentCardId = 0L;
        boolean isOrderUpdated = false;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_savepaymentcard(NULL,'");
            myQuery.append(payBean.getCardNo());
            myQuery.append("','");
            myQuery.append(payBean.getCardExpiryMonth());
            myQuery.append("','");
            myQuery.append(payBean.getCardExpiryYear());
            myQuery.append("','");
            myQuery.append(payBean.getBankId());
            myQuery.append("','");
            myQuery.append(payBean.getCardHolderName());
            myQuery.append("','");
            myQuery.append(payBean.getCardTypeId());
            myQuery.append("','");
            myQuery.append(orderId);
            myQuery.append("','");
            myQuery.append(cartId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    while (rs.next()) {
                        paymentCardId = rs.getLong("sp_CardId");
                        if (paymentCardId > 0) {
                            isOrderUpdated = true;
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isOrderUpdated;
        }
    }

    public boolean savePaymentBank(PaymentBean payBean, Long orderId, Long cartId) {
        Long paymentBankId = 0L;
        boolean isOrderUpdated = false;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_savepaymentbank (NULL,'");
            myQuery.append(payBean.getBankAccountNo());
            myQuery.append("','");
            myQuery.append(payBean.getBankId());
            myQuery.append("','");
            myQuery.append(orderId);
            myQuery.append("','");
            myQuery.append(cartId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    while (rs.next()) {
                        paymentBankId = rs.getLong("sp_PaymentBankId");
                        if (paymentBankId > 0) {
                            isOrderUpdated = true;
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isOrderUpdated;
        }
    }

    public boolean savePaymentCash(Long orderId, Long cartId) {
        boolean isOrderUpdated = false;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_saveorder('");
            myQuery.append(orderId);
            myQuery.append("','1',NULL,NULL,NULL,NULL,'");
            myQuery.append(cartId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            isOrderUpdated = true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isOrderUpdated;
        }
    }

    public boolean cancelOrder(Long orderId, Long cartId) {
        boolean isOrderCancelled = false;
        boolean isCartUpdated = false;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_saveOrder('");
            myQuery.append(orderId);
            myQuery.append("','3',NULL,NULL,NULL,NULL,NULL)");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                isOrderCancelled = true;
            }
            if (isOrderCancelled == true) {
                myQuery = new StringBuffer();
                myQuery.append("CALL sp_updatecart('");
                myQuery.append(cartId);
                myQuery.append("')");
                rs = dbc.executeProcedure(conn, myQuery.toString());
                if (rs != null) {
                    isCartUpdated = true;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isOrderCancelled;
        }
    }

    public PaymentBean[] getUserOrders(Long userId) {
        PaymentBean[] userOrderList = null;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectuserorder('");
            myQuery.append(userId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    userOrderList = setUserOrders(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return userOrderList;
        }
    }

    private PaymentBean[] setUserOrders(ResultSet rs) {
        List alOrders = new ArrayList();
        try {
            alOrders.clear();
            while (rs.next()) {
                payBean = new PaymentBean();
                payBean.setOrderId(rs.getLong("order_id"));
                payBean.setOrderDate(commUtil.formatDBDate(rs.getString("orderdate")));
                payBean.setOrderInvoiceAmt(rs.getString("invoiceamt"));
                payBean.setOrderStatus(rs.getString("orderstatus"));
                payBean.setOrderStatusId(rs.getLong("orderstatus_id"));
                payBean.setPaymentType(rs.getString("paymentmode"));
                payBean.setUserName(rs.getString("username"));
                alOrders.add(payBean);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return (PaymentBean[]) alOrders.toArray(new PaymentBean[0]);
        }
    }

    public PaymentBean getOrderDetail(Long orderId) {
        payBean = new PaymentBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectorder('");
            myQuery.append(orderId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    payBean = setOrderDetail(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return payBean;
        }
    }

    private PaymentBean setOrderDetail(ResultSet rs) {
        try {
            while (rs.next()) {
                payBean = new PaymentBean();
                payBean.setOrderId(rs.getLong("order_id"));
                payBean.setOrderDate(commUtil.formatDBDate(rs.getString("orderdate")));
                payBean.setOrderInvoiceAmt(rs.getString("invoiceamt"));
                payBean.setOrderStatus(rs.getString("orderstatus"));
                payBean.setOrderStatusId(rs.getLong("orderstatus_id"));
                payBean.setPaymentType(rs.getString("paymentmode"));
                payBean.setUserName(rs.getString("username"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return payBean;
        }
    }

    public PaymentBean[] getAllOrders() {
        PaymentBean[] ordersList = null;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectorder(NULL)");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    ordersList = setAllOrders(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return ordersList;
        }
    }

    private PaymentBean[] setAllOrders(ResultSet rs) {
        List alOrders = new ArrayList();
        try {
            alOrders.clear();
            while (rs.next()) {
                payBean = new PaymentBean();
                payBean.setOrderId(rs.getLong("order_id"));
                payBean.setOrderDate(commUtil.formatDBDate(rs.getString("orderdate")));
                payBean.setOrderInvoiceAmt(rs.getString("invoiceamt"));
                payBean.setOrderStatus(rs.getString("orderstatus"));
                payBean.setOrderStatusId(rs.getLong("orderstatus_id"));
                payBean.setPaymentType(rs.getString("paymentmode"));
                payBean.setUserName(rs.getString("username"));
                alOrders.add(payBean);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return (PaymentBean[]) alOrders.toArray(new PaymentBean[0]);
        }
    }

    public LinkedHashMap getOrderStatusLHMap() {
        LinkedHashMap orderStatusLHMap = new LinkedHashMap();
        myQuery = new StringBuffer();
        myQuery.append("SELECT 	`orderstatus_id`, 	`orderstatus`	 	FROM 	`retailws`.`orderstatus` ");
        orderStatusLHMap = commDAO.getData(myQuery.toString());
        return orderStatusLHMap;
    }

    public boolean updateOrderStatus(Long orderId, Long orderStatusId, Long cartId) {
        boolean isOrderStatusUpdated = false;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_saveOrder('");
            myQuery.append(orderId);
            myQuery.append("','");
            myQuery.append(orderStatusId);
            myQuery.append("',NULL,NULL,NULL,NULL,'");
            myQuery.append(cartId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                isOrderStatusUpdated = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isOrderStatusUpdated;
        }
    }
}
